package com.hd123.rumba.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FilterOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.List;

import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

@SuppressWarnings("restriction")
public class MidasUtil {

  public static void resultSet2DataPacket(ResultSet rs, OutputStream dp, List titles)
      throws SQLException, IOException {
    // titles = null;
    StringBuffer sb = new StringBuffer();
    dp.write("<DATAPACKET Version=\"2.0\">".getBytes("utf-8"));
    sb.append("<METADATA><FIELDS>");
    ResultSetMetaData rsmd = rs.getMetaData();
    for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
      sb.append("<FIELD attrname=\"" + getTitle(i, rsmd, titles) + "\" ");
      int type = rsmd.getColumnType(i);
      if (type == Types.NUMERIC || type == Types.DECIMAL || type == Types.INTEGER) {
        if (rsmd.getScale(i) > 0) {
          sb.append("fieldtype=\"fixed\" ");
          sb.append("DECIMALS=\"" + rsmd.getScale(i) + "\" ");
          sb.append("WIDTH=\"" + rsmd.getPrecision(i) + "\" ");
        } else if (rsmd.getScale(i) == 0) {
          sb.append("fieldtype=\"fixedFMT\" ");
          sb.append("DECIMALS=\"8\" ");
          sb.append("WIDTH=\"32\" ");
        } else {
          if (rsmd.getPrecision(i) < 32) {
            sb.append("fieldtype=\"fixed\" ");
            sb.append("WIDTH=\"" + rsmd.getPrecision(i) + "\" ");
          } else {
            sb.append("fieldtype=\"fixedFMT\" ");
            sb.append("DECIMALS=\"8\" ");
            sb.append("WIDTH=\"32\" ");
          }
        }
      } else if (type == Types.DATE || type == Types.TIMESTAMP) {
        sb.append("fieldtype=\"dateTime\" ");
      } else if (type == Types.VARCHAR) {
        sb.append("fieldtype=\"string\" ");
        sb.append("WIDTH=\"" + rsmd.getPrecision(i) + "\" ");
      } else if (type == Types.LONGVARCHAR) {
        sb.append("fieldtype=\"bin.hex\" ");
        sb.append("SUBTYPE=\"Text\" ");
        sb.append("WIDTH=\"1\" ");
      } else if (type == Types.BLOB) {
        sb.append("fieldtype=\"bin.hex\" ");
        sb.append("SUBTYPE=\"HBinary\" ");
        sb.append("WIDTH=\"1\" ");
      } else if (type == Types.CLOB) {
        sb.append("fieldtype=\"bin.hex\" ");
        sb.append("SUBTYPE=\"HMemo\" ");
        sb.append("WIDTH=\"1\" ");
      } else {
        throw new SQLException("cannot convert to datapacket: unknown type " + typeId(type));
      }
      sb.append("/>");
    }
    sb.append("</FIELDS></METADATA>");
    dp.write(sb.toString().getBytes("utf-8"));
    dp.write("<ROWDATA>".getBytes("utf-8"));
    BASE64Encoder encoder = new BASE64Encoder();
    while (rs.next()) {
      dp.write("<ROW ".getBytes("utf-8"));
      for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
        if (rs.getObject(i) == null)
          continue;
        dp.write((getTitle(i, rsmd, titles) + "=\"").getBytes("utf-8"));
        int type = rsmd.getColumnType(i);
        if (type == Types.BLOB) {
          encoder.encode(rs.getBlob(i).getBinaryStream(), new FilterOutputStream(dp) {
            public void write(int b) throws IOException {
              if (b != 13 && b != 10)
                super.write(b);
            }
          });
        } else
          dp.write(rs.getObject(i).toString().getBytes("UTF-8"));
        dp.write("\" ".getBytes("utf-8"));
      }
      dp.write("/>".getBytes("utf-8"));
    }
    dp.write("</ROWDATA>".getBytes("utf-8"));
    dp.write("</DATAPACKET>".getBytes("utf-8"));
  }

  private static String getTitle(int i, ResultSetMetaData rsmd, List titles) throws SQLException {
    if (i == 1) // i从1开始，第一列为Uuid
      return rsmd.getColumnName(i);
    else if (titles == null)
      return rsmd.getColumnName(i);
    else
      return (String) titles.get(i - 2); // titles不包含第一列Uuid
  }

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver((Driver) Class.forName("oracle.jdbc.driver.OracleDriver")
        .newInstance());
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@bitest:1521:orcl",
        "tangoyabo20070228", "xuxiangfeng");
    Statement stm = conn.createStatement();
    // testOracleTypes(stm);
    testResultSet2DataPacket(stm);
    System.out.println("done");
  }

  private static void decode() throws IOException {
    FileInputStream in = new FileInputStream("/temp/02.txt");
    FileOutputStream out = new FileOutputStream("/temp/02.txt.jpg");
    BASE64Decoder decoder = new BASE64Decoder();
    decoder.decodeBuffer(in, out);
    in.close();
    out.close();
  }

  private static void testResultSet2DataPacket(Statement stm) throws SQLException, IOException {
    ResultSet rs = stm
        .executeQuery("select sku,name,retailprice,specification, image from product where sku like '000000%'");
    FileOutputStream out = new FileOutputStream("c:/temp/02.dp.xml");
    out.write("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?>".getBytes());
    resultSet2DataPacket(rs, out, null);
    rs.close();
    out.close();
  }

  private static void testOracleTypes(Statement stm) throws SQLException, IOException {
    try {
      stm.execute("drop table testtypes");
    } catch (Exception e) {
    }
    StringBuffer sb = new StringBuffer();
    sb.append("create table testtypes (");
    sb.append("fint int, ");
    sb.append("fvarchar2_10 varchar2(10), ");
    sb.append("fvarchar_10 varchar(10),");
    sb.append("fdate date, ");
    sb.append("fdecimal_12_2 decimal(12,2),");
    sb.append("fnumber number, ");
    sb.append("fsmallint smallint,");
    sb.append("ffloat_2 float(2),");
    sb.append("ffloat float,");
    sb.append("freal real,");
    sb.append("fblob blob,");
    sb.append("fclob clob,");
    sb.append("flong long");
    sb.append(")");
    stm.execute(sb.toString());
    ResultSet rs = stm.executeQuery("select * from testtypes");
    System.out.println(printTypes(rs));
    PrintStream out = System.out;
    out.write("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?>".getBytes());
    resultSet2DataPacket(rs, out, null);
    rs.close();
  }

  private static String printTypes(ResultSet rs) throws SQLException {
    StringBuffer sb = new StringBuffer();
    ResultSetMetaData rsmd = rs.getMetaData();
    sb.append("<table>\n<tbody>\n<tr>");
    sb.append("<td>#</td>");
    sb.append("<td>getColumnName</td>");
    sb.append("<td>getColumnLabel</td>");
    sb.append("<td>getColumnType</td>");
    sb.append("<td>getColumnTypeName</td>");
    sb.append("<td>getColumnTypeClassName</td>");
    sb.append("<td>getColumnDisplaySize</td>");
    sb.append("<td>getScale</td>");
    sb.append("<td>getPrecision</td>");
    sb.append("</tr>\n");
    for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
      sb.append("<tr><td>" + i + "</td>");
      sb.append("<td>" + rsmd.getColumnName(i) + "</td>");
      sb.append("<td>" + rsmd.getColumnLabel(i) + "</td>");
      sb.append("<td>" + typeId(rsmd.getColumnType(i)) + "</td>");
      sb.append("<td>" + rsmd.getColumnTypeName(i) + "</td>");
      sb.append("<td>" + rsmd.getColumnClassName(i) + "</td>");
      sb.append("<td>" + rsmd.getColumnDisplaySize(i) + "</td>");
      sb.append("<td>" + rsmd.getScale(i) + "</td>");
      try {
        sb.append("<td>" + rsmd.getPrecision(i) + "</td></tr>\n");
      } catch (Exception e) {
        sb.append("<td>Exception</td></tr>\n");
      }
    }
    sb.append("</tr></tbody></table>");
    return sb.toString();
  }

  private static String typeId(int type) {
    if (type == Types.BIT) {
      return "BIT";
    } else if (type == Types.TINYINT) {
      return "TINYINT";
    } else if (type == Types.SMALLINT) {
      return "SMALLINT";
    } else if (type == Types.INTEGER) {
      return "INTEGER";
    } else if (type == Types.BIGINT) {
      return "BIGINT";
    } else if (type == Types.FLOAT) {
      return "FLOAT";
    } else if (type == Types.REAL) {
      return "REAL";
    } else if (type == Types.DOUBLE) {
      return "DOUBLE";
    } else if (type == Types.NUMERIC) {
      return "NUMERIC";
    } else if (type == Types.DECIMAL) {
      return "DECIMAL";
    } else if (type == Types.CHAR) {
      return "CHAR";
    } else if (type == Types.VARCHAR) {
      return "VARCHAR";
    } else if (type == Types.LONGVARCHAR) {
      return "LONGVARCHAR";
    } else if (type == Types.DATE) {
      return "DATE";
    } else if (type == Types.TIME) {
      return "TIME";
    } else if (type == Types.TIMESTAMP) {
      return "TIMESTAMP";
    } else if (type == Types.BINARY) {
      return "BINARY";
    } else if (type == Types.VARBINARY) {
      return "VARBINARY";
    } else if (type == Types.LONGVARBINARY) {
      return "LONGVARBINARY";
    } else if (type == Types.NULL) {
      return "NULL";
    } else if (type == Types.OTHER) {
      return "OTHER";
    } else if (type == Types.JAVA_OBJECT) {
      return "JAVA_OBJECT";
    } else if (type == Types.DISTINCT) {
      return "DISTINCT";
    } else if (type == Types.STRUCT) {
      return "STRUCT";
    } else if (type == Types.ARRAY) {
      return "ARRAY";
    } else if (type == Types.BLOB) {
      return "BLOB";
    } else if (type == Types.CLOB) {
      return "CLOB";
    } else if (type == Types.REF) {
      return "REF";
    } else if (type == Types.DATALINK) {
      return "DATALINK";
    } else if (type == Types.BOOLEAN) {
      return "BOOLEAN";
    }
    return null;
  }
}
